using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;

namespace SQLServerDetectionControl
{
    /// <summary>
    /// 
    /// </summary>
    public partial class SqlServersDetector : UserControl
    {
        private string dbConnectionString = @"Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        private SqlConnection dbConnection = null;
        private Int32 _RecordCount = 0;
        //private Boolean _Databases_SelectedIndexChanged = false; 
        //private Boolean _Servers_SelectedIndexChanged = false;
        //private Boolean _Tables_SelectedIndexChanged = false;

        public SqlServersDetector()
        {
            InitializeComponent();
            //grbServerBrowser.BackgroundImage


            comboServers.DrawMode = DrawMode.OwnerDrawFixed;
            comboServers.DrawItem += comboServers_DrawItem;
            comboServers.DropDownClosed += comboServers_DropDownClosed;

            comboDatabases.DrawMode = DrawMode.OwnerDrawFixed;
            comboDatabases.DrawItem += comboDatabases_DrawItem;
            comboDatabases.DropDownClosed += comboDatabases_DropDownClosed;

            cmbTables.DrawMode = DrawMode.OwnerDrawFixed;
            cmbTables.DrawItem += cmbTables_DrawItem;
            cmbTables.DropDownClosed += cmbTables_DropDownClosed;

            cmbColumn.DrawMode = DrawMode.OwnerDrawFixed;
            cmbColumn.DrawItem += cmbColumn_DrawItem;
            cmbColumn.DropDownClosed += cmbColumn_DropDownClosed;

        }

        private void btnLoadServers_Click(object sender, EventArgs e)
        {
            try
            {
                Cursor.Current = Cursors.WaitCursor;
                DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();
                comboServers.Items.Clear();
                for (int i = 0; i < servers.Rows.Count; i++)
                {
                    comboServers.Items.Add(servers.Rows[i]["ServerName"] + "\\" + servers.Rows[i]["InstanceName"]);
                    //comboDB2Ser.Items.Add(servers.Rows[i]["ServerName"] + "\\" + servers.Rows[i]["InstanceName"]);
                }
                if (comboServers.Items.Count > 0)
                {
                    comboServers.SelectedIndex = 0;
                    comboDatabases.Enabled = true;
                    btnGetLinkedServer.Enabled = true;
                }
                else
                {
                    comboDatabases.Enabled = false;
                    btnGetLinkedServer.Enabled = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Servers_SelectedIndexChanged = true;
                Cursor.Current = Cursors.Default;
            }
        }

        private void btnLoadDB_Click(object sender, EventArgs e)
        {
            GetDatabaseList();

        }

        ///
        /// Summary:
        ///     Displays the Database List to the user.
        private void GetDatabaseList()
        {
            Login dlg = new Login();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    Username = dlg.Username;
                    Password = dlg.Password;

                    dbConnectionString = "Data Source=" + comboServers.Text + ";";
                    dbConnectionString = dbConnectionString + " User ID=" + dlg.Username + "; Password=" + dlg.Password + ";";

                    dbConnection = new SqlConnection(dbConnectionString);
                    dbConnection.Open();
                    DataTable database = dbConnection.GetSchema("Databases");

                    comboDatabases.DataSource = database;
                    comboDatabases.DisplayMember = "Database_Name";
                    if (comboDatabases.Items.Count > 0)
                    {
                        comboDatabases.SelectedIndex = 0;
                        cmbTables.Enabled = true;
                        Servers_SelectedIndexChanged = false;
                        IsTableListLoaded = true;
                    }
                    else
                        cmbTables.Enabled = false;
                    dbConnection.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    Databases_SelectedIndexChanged = true;
                    Servers_SelectedIndexChanged = true;
                }
            }
            else
            {
                MessageBox.Show("Please provide username and password.", "Error");
            }
        }

        /// <summary>
        /// Displays the Table List to the user.
        /// </summary>
        private void GetTableList()
        {

            String ConnectionString = String.Format("Server={0};" +
                                       "Database={1};" +
                                       "Trusted_Connection=False;" +
                                       "User ID={2};" +
                                       "Password={3};" +
                                       "Persist Security Info=True;" +
                                      "Connection Timeout=100000;", SelectedServer, SelectedDatabase, Username, Password);

            using (SqlConnection connection = new SqlConnection(@ConnectionString))
            {

                connection.Open();

                using (SqlCommand command = connection.CreateCommand())
                {

//                    command.CommandText =
//                    @"SELECT s.name as SchemaName, o.name as TableName,o.type_desc as TableType
//                    FROM sys.objects o WITH(NOLOCK)
//                    JOIN sys.schemas s WITH(NOLOCK)
//                      ON o.schema_id = s.schema_id
//                    WHERE o.is_ms_shipped = 0 AND RTRIM(o.type) = 'U'
//                    ORDER BY s.name ASC, o.name ASC";

                    command.CommandText = @"SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM information_schema.tables  where SUBSTRING(TABLE_NAME,1,1) != '_' AND TABLE_TYPE='BASE TABLE' order by TABLE_TYPE, TABLE_NAME";
                    //Table Description
                    //SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLLATION_NAME FROM information_schema.COLUMNS 

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        cmbTables.Items.Clear();
                        while (reader.Read())
                        {
                            string schemaName = reader.GetString(1);
                            string tableName = reader.GetString(2);
                            cmbTables.Items.Add(tableName);
                        }
                    }

                    if (cmbTables.Items.Count > 0)
                    {
                        IsTableListLoaded = true;
                        cmbTables.SelectedIndex = 0;
                    }
                }
            }
        }

        //
        // Summary:
        //     Displays Table Description to the user.

        private void GetTableDescription()
        {
            String ConnectionString = String.Format("Server={0};" +
                                       "Database={1};" +
                                       "Trusted_Connection=False;" +
                                       "User ID={2};" +
                                       "Password={3};" +
                                       "Persist Security Info=True;" +
                                      "Connection Timeout=100000;", SelectedServer, SelectedDatabase, Username, Password);

            using (SqlConnection connection = new SqlConnection(@ConnectionString))
            {

                connection.Open();

                using (SqlCommand command = connection.CreateCommand())
                {

                    //Table List
                    //                    command.CommandText =
                    //                    @"SELECT s.name as SchemaName, o.name as TableName,o.type_desc as TableType
                    //                    FROM sys.objects o WITH(NOLOCK)
                    //                    JOIN sys.schemas s WITH(NOLOCK)
                    //                      ON o.schema_id = s.schema_id
                    //                    WHERE o.is_ms_shipped = 0 AND RTRIM(o.type) = 'U'
                    //                    ORDER BY s.name ASC, o.name ASC";

                    //Table List
                    //command.CommandText = @"SELECT * FROM information_schema.tables  where SUBSTRING(TABLE_NAME,1,1) != '_' AND TABLE_TYPE='BASE TABLE' order by TABLE_TYPE, TABLE_NAME";

                    //Table Description
                    command.CommandText = @"SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLLATION_NAME FROM information_schema.COLUMNS ";

                    using (SqlDataReader reader = command.ExecuteReader())
                    {

                        while (reader.Read())
                        {

                            string schemaName = reader.GetString(0);
                            string tableName = reader.GetString(1);
                            cmbTables.Items.Add(tableName);
                        }

                        if (cmbTables.Items.Count > 0)
                            cmbTables.SelectedIndex = 0;
                    }
                }
            }
        }
        /// <summary>
        /// Populates the combo.
        /// </summary>
        /// <param name="cbo">The cbo.</param>
        /// <param name="dst">The DST.</param>
        public void PopulateCombo(ref System.Windows.Forms.ComboBox cbo, DataSet dst)
        {
            //Calls the full method using our assumptions
            PopulateCombo(ref cbo, dst, dst.Tables[0].Columns[0].ToString(), dst.Tables[0].Columns[1].ToString(), dst.Tables[0].TableName.ToString());
        }

        //The complete method that populates a ComboBox reference with
        //data from the created DataSet.  This is the full method.

        /// <summary>
        /// Populates the combo.
        /// </summary>
        /// <param name="cbo">The cbo.</param>
        /// <param name="dst">The DST.</param>
        /// <param name="strValueMember">The STR value member.</param>
        /// <param name="strDisplayMember">The STR display member.</param>
        /// <param name="strTableName">Name of the STR table.</param>
        private void PopulateCombo(ref ComboBox cbo, DataSet dst, String strValueMember, String strDisplayMember, String strTableName)
        {
            cbo.DataSource = dst.Tables[strTableName];
            cbo.DisplayMember = strDisplayMember;
            cbo.ValueMember = strValueMember;
        }

        /// <summary>
        /// Gets the column list.
        /// </summary>
        /// <param name="cbo">The cbo.</param>
        /// <returns></returns>
        public List<string> GetColumnList(ref System.Windows.Forms.ComboBox cbo)
        {
            List<string> ColumnName = new List<string>();
            if (IsTableListLoaded)
            {
                //DataTable dtDataTable;
                DataSet dsDataSet;

                //SELECT COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = 'AMB_FORFA1'
                String ConnectionString = String.Format("Server={0};" +
                                           "Database={1};" +
                                           "Trusted_Connection=False;" +
                                           "User ID={2};" +
                                           "Password={3};" +
                                           "Persist Security Info=True;" +
                                          "Connection Timeout=100000;", SelectedServer, SelectedDatabase, Username, Password);

                using (SqlConnection connection = new SqlConnection(@ConnectionString))
                {

                    connection.Open();

                    using (SqlCommand command = connection.CreateCommand())
                    {

                        //Column Description
                        command.CommandText = String.Format(@"SELECT COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME = '{0}'", SelectedTable);
                        SqlDataAdapter daDataAdapter = new SqlDataAdapter(command.CommandText, connection);

                        dsDataSet = new DataSet(SelectedTable);
                        daDataAdapter.Fill(dsDataSet);

                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            ColumnName.Clear();
                            cbo.Items.Clear();
                            while (reader.Read())
                            {
                                ColumnName.Add(reader.GetString(0));
                                cbo.Items.Add(reader.GetString(0));
                            }
                            if (cbo.Items.Count > 0)
                            {
                                cbo.SelectedIndex = 0;
                                cbo.Enabled = true;
                            }
                            else
                                cbo.Enabled = false;
                        }
                    }
                }
            }
            return ColumnName;
        }

        /// <summary>
        /// Gets the DB connection string.
        /// </summary>
        /// <value>
        /// The DB connection string.
        /// </value>
        /// <exception cref="System.Exception">Servers OR Databases might not be loaded yet.</exception>
        public string DBConnectionString
        {
            get
            {
                if (dbConnectionString != null)
                    return String.Format(@"Server={0};Database={1};User Id={2};Password={3};", comboServers.Text,comboDatabases.Text, Username, Password) ;
                    //return dbConnectionString + "Initial Catalog=" + comboDatabases.Text;
                else
                    throw new Exception("Servers OR Databases might not be loaded yet.");
            }
        }
        /// <summary>
        /// Gets or sets the main label.
        /// </summary>
        /// <value>
        /// The main label.
        /// </value>
        public string MainLabel
        {
            get
            {
                return grbServerBrowser.Text;
            }
            set
            {
                grbServerBrowser.Text = value;
            }
        }
        public string ServersLabel
        {
            get
            {
                return lblServer.Text;
            }
            set
            {
                lblServer.Text = value;
            }
        }
        public string DatabasesLabel
        {
            get
            {
                return lblDatabase.Text;
            }
            set
            {
                lblDatabase.Text = value;
            }
        }
        public string TableLabel
        {
            get
            {
                return lblTableList.Text;
            }
            set
            {
                lblTableList.Text = value;
            }
        }

        public string SelectedServer
        {
            get
            {
                return comboServers.Text;
            }
        }
        public string SelectedDatabase
        {
            get
            {
                return comboDatabases.Text;
            }
        }
        public string SelectedTable
        {
            get
            {
                return cmbTables.Text;
            }
        }

        public bool IsDatabaseListVisible
        {
            set
            {
                comboDatabases.Visible = value;
                lblDatabase.Visible = value;
            }
            get
            {
                return comboDatabases.Visible;
            }
        }
        
        public bool IsTableListVisible
        {
            set 
            {
                cmbTables.Visible = value;
                lblTableList.Visible = value;
                GroupServerBrowserSize(value);
            }
            get
            {
                return cmbTables.Visible;
            }
        }

        public bool IsColumnListVisible
        {
            set
            {
                cmbColumn.Visible = value;
                lblColumn.Visible = value;
            }
            get
            {
                return cmbColumn.Visible;
            }
        }
        
        private void GroupServerBrowserSize(bool value)
        {
            switch (value)
            {
                case true:
                    grbServerBrowser.Width = 276;
                    grbServerBrowser.Height = 111;
                    break;
                case false:
                    grbServerBrowser.Width = 276;
                    grbServerBrowser.Height = 78;
                    break;
            }
        }

        override public Image BackgroundImage
        {
            set
            {
                grbServerBrowser.BackgroundImage = value;
            }
        }


        /// <summary>
        /// Gets or sets the username.
        /// </summary>
        /// <value>
        /// The username.
        /// </value>
        public string Username
        { get; set; }
        public string Password
        { get; set; }

        private Boolean Databases_SelectedIndexChanged
        { get; set; }

        private Boolean Servers_SelectedIndexChanged
        { get; set; }

        private Boolean Tables_SelectedIndexChanged
        { get; set; }

        private Boolean IsTableListLoaded
        { get; set; }

        public Int32 RecordCount
        {
            set
            {
                _RecordCount = value;
            }
            get
            {
                return _RecordCount;
            }
        }

        private void comboServers_SelectedIndexChanged(object sender, EventArgs e)
        {
            Servers_SelectedIndexChanged = true;
            comboDatabases.DataSource = null;
            comboDatabases.Items.Clear();
            comboDatabases.Text = string.Empty;
            comboDatabases.SelectedIndex = -1;
        }

        private void comboDatabases_Click(object sender, EventArgs e)
        {
            if (!IsTableListLoaded)
            {
                Cursor.Current = Cursors.WaitCursor;
                if (!String.IsNullOrEmpty(comboServers.Text))
                    if (Servers_SelectedIndexChanged)
                    {
                        GetDatabaseList();
                    }
                Cursor.Current = Cursors.Default;
            }
        }
        private void comboDatabases_SelectedIndexChanged(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            Databases_SelectedIndexChanged = true;
            cmbTables.DataSource = null;
            cmbTables.Items.Clear();
            IsTableListLoaded = false;
            cmbTables.Text = string.Empty;
            cmbTables.SelectedIndex = -1;
            Cursor.Current = Cursors.Default;
        }

        private void cmbTables_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            if ( !String.IsNullOrEmpty(comboDatabases.Text))
                if (Databases_SelectedIndexChanged)
                    GetTableList();
            Databases_SelectedIndexChanged = false;
            Cursor.Current = Cursors.Default;
        }
        private void cmbTables_SelectedIndexChanged(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            Tables_SelectedIndexChanged = true;
            cmbColumn.DataSource = null;
            cmbColumn.Items.Clear();
            GetColumnList(ref cmbColumn);
            cmbColumn.Text = string.Empty;
            cmbColumn.SelectedIndex = -1;
            Cursor.Current = Cursors.Default;
        }

        private Int32 GetRecordCount()
        {
            string ConnectionString = DBConnectionString;
            SqlConnection theSqlServerConnection = new SqlConnection(ConnectionString);
            
            try
            {
                theSqlServerConnection.Open();
                //SqlCommand theSqlCommand = new SqlCommand(String.Format("SELECT TOP(10) * FROM [{0}].[dbo].[{1}]", sqlServersDetector.SelectedDatabase, sqlServersDetector.SelectedTable), theSqlServerConnection);
                String sSQL = String.Format("SELECT COUNT(*) FROM [{0}].[{1}].[dbo].[{2}]", SelectedServer, SelectedDatabase, SelectedTable);
                SqlDataAdapter daDataAdapter = new SqlDataAdapter(sSQL, theSqlServerConnection);
                DataTable dtDataTable = new DataTable(SelectedTable);
                daDataAdapter.Fill(dtDataTable);
                RecordCount = dtDataTable.Rows.Count;//dtDataTable.DataSet.Tables[0].Rows.Count
                return RecordCount;
            }
            catch (Exception Ex)
            {
                MessageBox.Show(Ex.Message);
                RecordCount = 0;
            }
            finally
            {
                theSqlServerConnection.Close();
            }
            return RecordCount;
        }

        private void btnGetLinkedServer_Click(object sender, EventArgs e)
        {
            GetLinkedServer();
        }

        private void GetLinkedServer()
        {
            //select server_id, name, product, provider from sys.servers where is_linked = 0
            //select * from sys.servers where is_linked = 1

            Login dlg = new Login();
            if (dlg.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    Username = dlg.Username;
                    Password = dlg.Password;
                    String ConnectionString = String.Format("Server={0};" +
                                               "Database={1};" +
                                               "Trusted_Connection=False;" +
                                               "User ID={2};" +
                                               "Password={3};" +
                                               "Persist Security Info=True;" +
                                              "Connection Timeout=100000;", comboServers.Text, SelectedDatabase, Username, Password);

                    using (SqlConnection connection = new SqlConnection(@ConnectionString))
                    {

                        connection.Open();

                        using (SqlCommand command = connection.CreateCommand())
                        {

                            //                    command.CommandText =
                            //                    @"SELECT s.name as SchemaName, o.name as TableName,o.type_desc as TableType
                            //                    FROM sys.objects o WITH(NOLOCK)
                            //                    JOIN sys.schemas s WITH(NOLOCK)
                            //                      ON o.schema_id = s.schema_id
                            //                    WHERE o.is_ms_shipped = 0 AND RTRIM(o.type) = 'U'
                            //                    ORDER BY s.name ASC, o.name ASC";

                            command.CommandText = @"SELECT server_id, name, product, provider from sys.servers";
                            //Table Description
                            //SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLLATION_NAME FROM information_schema.COLUMNS 

                            using (SqlDataReader reader = command.ExecuteReader())
                            {
                                comboServers.Items.Clear();
                                while (reader.Read())
                                {
                                    string ServerName = reader.GetString(1);
                                    string ProductName = reader.GetString(2);
                                    string ProviderName = reader.GetString(3);

                                    comboServers.Items.Add(ServerName);
                                }
                            }

                            if (comboServers.Items.Count > 0)
                            {
                                comboServers.SelectedIndex = 0;
                            }
                        }
                    }
                }
                catch (Exception Ex)
                {
                    MessageBox.Show(Ex.Message);
                }
            }
        }

        private void comboServers_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index < 0) { return; } // added this line thanks to Andrew's comment
            string text = comboServers.GetItemText(comboServers.Items[e.Index]);
            e.DrawBackground();
            using (SolidBrush br = new SolidBrush(e.ForeColor))
            { e.Graphics.DrawString(text, e.Font, br, e.Bounds); }
            if ((e.State & DrawItemState.Selected) == DrawItemState.Selected)
            { toolTip1.Show(text, comboServers, e.Bounds.Right, e.Bounds.Bottom); }
            e.DrawFocusRectangle();

        }

        private void comboDatabases_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index < 0) { return; } // added this line thanks to Andrew's comment
            string text = comboDatabases.GetItemText(comboDatabases.Items[e.Index]);
            e.DrawBackground();
            using (SolidBrush br = new SolidBrush(e.ForeColor))
            { e.Graphics.DrawString(text, e.Font, br, e.Bounds); }
            if ((e.State & DrawItemState.Selected) == DrawItemState.Selected)
            { toolTip1.Show(text, comboDatabases, e.Bounds.Right, e.Bounds.Bottom); }
            e.DrawFocusRectangle();

        }

        private void cmbTables_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index < 0) { return; } // added this line thanks to Andrew's comment
            string text = cmbTables.GetItemText(cmbTables.Items[e.Index]);
            e.DrawBackground();
            using (SolidBrush br = new SolidBrush(e.ForeColor))
            { e.Graphics.DrawString(text, e.Font, br, e.Bounds); }
            if ((e.State & DrawItemState.Selected) == DrawItemState.Selected)
            { toolTip1.Show(text, cmbTables, e.Bounds.Right, e.Bounds.Bottom); }
            e.DrawFocusRectangle();

        }

        private void cmbColumn_DrawItem(object sender, DrawItemEventArgs e)
        {
            if (e.Index < 0) { return; } // added this line thanks to Andrew's comment
            string text = cmbColumn.GetItemText(cmbColumn.Items[e.Index]);
            e.DrawBackground();
            using (SolidBrush br = new SolidBrush(e.ForeColor))
            { e.Graphics.DrawString(text, e.Font, br, e.Bounds); }
            if ((e.State & DrawItemState.Selected) == DrawItemState.Selected)
            { toolTip1.Show(text, cmbColumn, e.Bounds.Right, e.Bounds.Bottom); }
            e.DrawFocusRectangle();

        }

        private void comboServers_DropDownClosed(object sender, EventArgs e)
        {
            toolTip1.Hide(comboServers);
        }

        private void comboDatabases_DropDownClosed(object sender, EventArgs e)
        {
            toolTip1.Hide(comboDatabases);
        }

        private void cmbTables_DropDownClosed(object sender, EventArgs e)
        {
            toolTip1.Hide(cmbTables);
        }

        private void cmbColumn_DropDownClosed(object sender, EventArgs e)
        {
            toolTip1.Hide(cmbColumn);
        }

    }
}
//